﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using LibrarySolution.DB;
using LibrarySolution.Model.基础;
using LibrarySolution.ComMethod;

namespace LibrarySolution.DAO.基础
{
    class BkxxDao
    {
        String sql;
        List<SqlParameter> list;
        DataTable dt = new DataTable();
        //private static StringBuilder SqlBuilder = new StringBuilder(
        //    "select bk_code as 书本编码,bk_no as 书本编号,bk_name as 书本名称,bk_writer as 作者,bk_publish as 出版社," +
        //    "bk_price as 价格,bk_cbsj as 出版时间,bk_rksj as 入库时间,bk_state as 状态,yh_name as 用户姓名 from jc_bkxx left outer join " +
        //    "jc_yhxx on bk_yhno = yh_no");
        //private static int SBlenth = SqlBuilder.Length;

        public DataTable getDataTable(BkxxDetail bkxxDetail)
        {
            BkxxQuery(bkxxDetail);
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public DataTable getDTbyBkName(BkxxDetail bkxxDetail)
        {
            BkxxQuery(bkxxDetail);
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public DataTable getDtbyWriter(BkxxDetail bkxxDetail)
        {
            BkxxQuery(bkxxDetail);
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public DataTable getDtbyBkNo(BkxxDetail bkxxDetail)
        {
            BkxxQuery(bkxxDetail);
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public DataTable getBkxxBb(BkxxDetail bkxxDetail)
        {
            BkxxQuery(bkxxDetail);
            sql = "select bk_no as 图书编号,bk_name as 图书名称,bk_bjcs as 被借次数 from jc_bkxx";
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public void DelSelectRow(string bkNO)
        {
            String sql = "delete from jc_bkxx where bk_no = @bkNo";
            SqlParameter[] parameters = { new SqlParameter("@bkNo", bkNO) };
            DBExecuteUtil.querySqlV(sql, parameters);
        }

        public BkxxDetail getBkxxDetialByNo(BkxxDetail bkxxDetail)
        {
            String[][] result;
            BkxxQuery(bkxxDetail);
            result = DBExecuteUtil.querySqlArray(sql, list.ToArray());
            if (result != null)
            {
                bkxxDetail.bk_code = result[0][0];
                bkxxDetail.bk_no = result[0][1];
                bkxxDetail.bk_name = result[0][2];
                bkxxDetail.bk_writer = result[0][3];
                bkxxDetail.bk_publish = result[0][4];
                bkxxDetail.bk_state = result[0][5];
                bkxxDetail.bk_price = result[0][6];
                bkxxDetail.bk_cbsj = result[0][7];
                bkxxDetail.bk_rksj = result[0][8];
                bkxxDetail.bk_yhname = result[0][9];
            }
            return bkxxDetail;
        }

        public String getMaxNo()
        {
            String result;
            sql = "select max(bk_no) from jc_bkxx";
            result = DBExecuteUtil.querySql(sql, null);
            return Convert.ToString(int.Parse(result) + 1);
        }

        public String getBkCount()
        {
            String result;
            sql = "select count(*) from jc_bkxx where bk_state = '在库'";
            result = DBExecuteUtil.querySql(sql, null);
            return result;
        }

        public String getMaxCode()
        {
            String result;
            sql = "select max(bk_code) from jc_bkxx";
            result =DBExecuteUtil.querySql(sql,null);
            return Convert.ToString(int.Parse(result) + 1);
        }

        public String getYhNo(String yhName)
        {
            String result;
            sql ="select yh_no from jc_yhxx ,jc_bkxx where yh_no = bk_yhno and bk_no = @bkNo";
            result = DBExecuteUtil.querySql(sql, list.ToArray());
            return result;
        }

        public void Dsbj(BkxxDetail bkxxEntity)
        {
            BkxxQuery(bkxxEntity);
            sql = "update jc_bkxx set bk_state = '丢失' where bk_no=@bkNo and bk_yhno=@bkYhno";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public void UpdateBkxx(BkxxDetail bkxxDetail)
        {
            BkxxQuery(bkxxDetail);
            //if (bkxxDetail.bk_yhno != null && bkxxDetail.bk_yhno != "")
            //{
            //    sql = "update jc_bkxx set bk_no=@bkNo, bk_name=@bkName, bk_writer=@Writer, bk_publish=@Publish," +
            //      " bk_price=@Price, bk_cbsj=@bkCbsj, bk_rksj=@bkRksj, bk_state=@State where bk_code = @bkcode";
            //}
            //else
            //{
            //    sql = "update jc_bkxx set bk_no=@bkNo, bk_name=@bkName, bk_writer=@Writer, bk_publish=@Publish," +
            //      " bk_price=@Price, bk_cbsj=@bkCbsj, bk_rksj=@bkRksj, bk_state=@State where bk_code = @bkcode";
            //}

            sql = "update jc_bkxx set bk_name=@bkName, bk_writer=@Writer, bk_publish=@Publish," +
              " bk_price=@Price, bk_cbsj=@bkCbsj, bk_rksj=@bkRksj, bk_state=@State where bk_no = @bkNo";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public void MarkLost(BkxxDetail bkxxDetail)
        {
            BkxxQuery(bkxxDetail);
            sql = "update jc_bkxx set bk_state = @State where bk_no=@bkNo";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public void insertBkxx(BkxxDetail bkxxDetail)
        {
            BkxxQuery(bkxxDetail);
            //sql = "insert into jc_bkxx (bk_no,bk_name,bk_writer,bk_publish,bk_price,bk_cbsj,bk_rksj," +
            //"bk_state) values (@bkNo, @bkName, @Writer, @Publish, @Price, @bkCbsj, @bkRksj, @State)";
            sql = "insert into jc_bkxx values (@bkNo, @bkName, @Writer, @Publish, @Price, @bkCbsj, @bkRksj, @State,null,0)";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        // 公共查询语句
        private void BkxxQuery(BkxxDetail bkxxDetail)
        {
            list = new List<SqlParameter>();
            sql = "select bk_code as 书本编码,bk_no as 书本编号,bk_name as 书本名称,bk_writer as 作者,bk_publish as 出版社," +
            "bk_state as 状态,bk_price as 价格,bk_cbsj as 出版时间,bk_rksj as 入库时间,yh_name as 用户姓名,bk_bjcs"+
            " from jc_bkxx left outer join jc_yhxx on bk_yhno = yh_no where bk_no = bk_no";

            String result;
            if (bkxxDetail.bk_name != null)
            {
                list.Add(new SqlParameter("@bkName", bkxxDetail.bk_name));
                sql += " and bk_name like '%' + @bkName + '%'";
            }

            if (bkxxDetail.bk_no != null)
            {
                list.Add(new SqlParameter("@bkNo", bkxxDetail.bk_no));
                sql += " and bk_no = @bkNo";
            }

            if (bkxxDetail.bk_writer != null)
            {
                list.Add(new SqlParameter("@Writer", bkxxDetail.bk_writer));
                sql += " and bk_writer like '%' + @Writer + '%'";
            }

            if (bkxxDetail.bk_yhname != null && bkxxDetail.bk_yhname != "")
            {
                result = getYhNo(bkxxDetail.bk_yhname);
                bkxxDetail.bk_yhno = result;
                list.Add(new SqlParameter("@bkYhNo", result));
            }

            if (bkxxDetail.bk_publish != null)
            {
                list.Add(new SqlParameter("@Publish", bkxxDetail.bk_publish));
                //sql += " and bk_publish = @Publish";
            }
            if (bkxxDetail.bk_price != null)
            {
                list.Add(new SqlParameter("@Price", bkxxDetail.bk_price));
                //sql += " and bk_price = @Price";
            }
            if (bkxxDetail.bk_cbsj != null)
            {
                list.Add(new SqlParameter("@bkCbsj", bkxxDetail.bk_cbsj));
                //sql += " and bk_cbsj = @bkCbsj";
            }
            if (bkxxDetail.bk_rksj != null)
            {
                list.Add(new SqlParameter("@bkRksj", bkxxDetail.bk_rksj));
                //sql += " and bk_rksj = @bkRcsj";
            }
            if (bkxxDetail.bk_state != null)
            {
                list.Add(new SqlParameter("@State", bkxxDetail.bk_state));
                //sql += " and bk_state = @State";
            }
            if (bkxxDetail.bk_yhno != null)
            {
                list.Add(new SqlParameter("@bkYhno", bkxxDetail.bk_yhno));
            }
            if (GlobalVar.SpGly == false && GlobalVar.NoGly == false)
            { sql += " and bk_state = '在库'"; }
        }
    }
}
